/**
 * @PROJECT 
 * @DATE 2018年1月21日 下午10:00:23
 * @AUTHOR LUYU
 */
package com.kool.devops.service;

import com.kool.core.exception.AppException;
import com.kool.core.util.DateUtils;
import com.kool.core.util.PropertyUtils;
import com.kool.devops.bean.vo.ColumnBean;
import com.kool.devops.bean.vo.TableBean;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @DESCRIBE
 * @AUTHOR LUYU
 * @DATE 2018年1月21日 下午10:00:23
 *
 */
@Service
public class DevToolsService {
	public String excelToSql(File file) throws AppException {

		List<List<List<String>>> listSheet = getExcelValue(file, 1, 65535, 0, 65535, 0, 65535);

		StringBuffer sbSql = new StringBuffer();
		for (List<List<String>> sheet : listSheet) {
			String sql = TableBeanToSql(sheetValueToTableBean(sheet));
			sbSql.append(sql);
			sbSql.append("\r\n");
		}
		return sbSql.toString();
	}

	/**
	 * 
	 * @DESCRIBE 读取excel内容
	 * @DATE 2017年8月25日 下午9:04:30
	 *
	 * @param file
	 *            文件路径
	 * @param sheetBegin
	 *            需要读取的sheet起始序号，0开始
	 * @param sheetEnd
	 *            需要读取的sheet末尾序号
	 * @param rowBegin
	 *            需要读取的开始行序号，0开始
	 * @param rowEnd
	 *            需要读取的末尾行序号
	 * @param cellBegin
	 *            需要读取的单元格开始序号，0开始
	 * @param cellEnd
	 *            需要读取的单元格末尾序号
	 * @return
	 * @throws IOException
	 */
	public static List<List<List<String>>> getExcelValue(File file, int sheetBegin, int sheetEnd, int rowBegin,
			int rowEnd, int cellBegin, int cellEnd) throws AppException {
		FileInputStream fis = null;
		Workbook book = null;
		List<List<List<String>>> listSheet = null;
		try {
			fis = new FileInputStream(file);
			if (file.getPath().endsWith("xls")) {
				book = new HSSFWorkbook(fis);
			} else if (file.getPath().endsWith("xlsx")) {
				book = new XSSFWorkbook(fis);
			}
			listSheet = new ArrayList<List<List<String>>>();
			if (sheetEnd > book.getNumberOfSheets()) {
				sheetEnd = book.getNumberOfSheets();
			}
			for (int i = sheetBegin; i < sheetEnd; i++) {
				List<List<String>> sheet = getSheetValue(book, i, rowBegin, rowEnd, cellBegin, cellEnd);
				listSheet.add(sheet);
			}
			book.close();
		} catch (FileNotFoundException e) {
			throw new AppException("");
		} catch (IOException e) {
			throw new AppException("");
		}
		return listSheet;
	}

	/**
	 * 
	 * @DESCRIBE 获取excel的一页数据
	 * @DATE 2017年8月25日 下午9:07:57
	 *
	 * @param book
	 * @param sheetIndex
	 * @param rowBegin
	 * @param rowEnd
	 * @param cellBegin
	 * @param cellEnd
	 * @return
	 * @throws IOException
	 */
	private static List<List<String>> getSheetValue(Workbook book, int sheetIndex, int rowBegin, int rowEnd,
			int cellBegin, int cellEnd) throws IOException {
		Sheet sheet = book.getSheetAt(sheetIndex);
		List<List<String>> listRow = new ArrayList<List<String>>();
		if (rowEnd > sheet.getLastRowNum()) {
			rowEnd = sheet.getLastRowNum();
		}
		for (int i = rowBegin; i <= rowEnd; i++) {
			Row row = sheet.getRow(i);
			List<String> listCell = new ArrayList<String>();
			if (cellEnd > row.getLastCellNum()) {
				cellEnd = row.getLastCellNum();
			}
			for (int j = 0; j < cellEnd; j++) {
				Cell cell = row.getCell(j);
				String cellValue = getCellValue(cell);
				listCell.add(cellValue);
			}
			listRow.add(listCell);
		}

		return listRow;
	}

	/**
	 * 
	 * @DESCRIBE 表结构数据转sql文件，并保存到目标文件夹
	 * @DATE 2017年8月25日 下午9:10:23
	 *
	 * @param tableBean
	 * @throws IOException
	 * @throws Exception
	 */
	private static String TableBeanToSql(TableBean tableBean) throws AppException {
		String templatePath = PropertyUtils.getValue("CREATE_TABLE_SQL_TEMPLATE");
		Configuration cfg = new Configuration(Configuration.DEFAULT_INCOMPATIBLE_IMPROVEMENTS);
		cfg.setDefaultEncoding("UTF-8");
		String sql = null;
		try {
			cfg.setDirectoryForTemplateLoading(new File(templatePath));
			Template t = cfg.getTemplate("table.ftl");
			StringWriter writer = new StringWriter();   
			t.process(tableBean, writer);
			sql = writer.toString();
		} catch (IOException e) {
			throw new AppException("");
		} catch (TemplateException e) {
			throw new AppException("");
		}
		return sql;
	}

	/**
	 * 
	 * @DESCRIBE excel一页内容转成表结构数据
	 * @DATE 2017年8月25日 下午9:11:17
	 *
	 * @param list
	 * @return
	 * @throws Exception
	 */
	private static TableBean sheetValueToTableBean(List<List<String>> list) throws AppException {
		TableBean tableBean = new TableBean();
		String createTime = DateUtils.getCurrentTimeString(DateUtils.FORMAT_TIMESTAMP);
		String tableId = list.get(0).get(1);
		String tableName = list.get(0).get(2);
		tableBean.setCreateTime(createTime);
		tableBean.setTableId(tableId);
		tableBean.setTableName(tableName);

		ArrayList<ColumnBean> columns = new ArrayList<ColumnBean>();
		ArrayList<String> pks = new ArrayList<String>();
		for (int i = 2; i < list.size(); i++) {
			List<String> listCell = list.get(i);
			if (listCell.get(0) == null || listCell.get(0).equals("")) {
				continue;
			}
			String colId = listCell.get(0);
			String colHumpName = listCell.get(1);
			String colName = listCell.get(2);
			String colType = listCell.get(3);
			String colIsNullable = listCell.get(4);
			String colIsKey = listCell.get(5);
			String colComment = listCell.get(6);

			ColumnBean column = new ColumnBean();
			column.setId(colId);
			column.setHumpName(colHumpName);
			column.setName(colName);
			column.setType(colType);
			column.setIsNullable(colIsNullable);
			column.setIsKey(colIsKey);
			column.setComment(colComment);

			columns.add(column);

			if ("Y".equals(colIsKey)) {
				pks.add(colId);
			}
		}
		tableBean.setColumns(columns);
		tableBean.setPks(pks);

		return tableBean;
	}

	/**
	 * @DESCRIBE 转单元格数据的格式，暂时只需要String类型
	 * @DATE 2017-08-23 21:35:42
	 *
	 * @param cell
	 * @return
	 */
	private static String getCellValue(Cell cell) {
		cell.setCellType(CellType.STRING);
		return String.valueOf(cell.getStringCellValue());
	}

	/**
	 * @DESCRIBE 
	 * @DATE 2018年1月21日 下午10:56:22
	 *
	 * @param uploadFile
	 * @return
	 */
	public String excelToSql(MultipartFile uploadFile) throws AppException {
		Workbook book = null;
		List<List<List<String>>> listSheet = null;
		try {
			InputStream is = uploadFile.getInputStream();
			if (uploadFile.getOriginalFilename().endsWith("xls")) {
				book = new HSSFWorkbook(is);
			} else if (uploadFile.getOriginalFilename().endsWith("xlsx")) {
				book = new XSSFWorkbook(is);
			}
			listSheet = new ArrayList<List<List<String>>>();
			int sheetEnd = 65535;
			if (sheetEnd > book.getNumberOfSheets()) {
				sheetEnd = book.getNumberOfSheets();
			}
			for (int i = 1; i < sheetEnd; i++) {
				List<List<String>> sheet = getSheetValue(book, i, 0, 65535, 0, 65535);
				listSheet.add(sheet);
			}
			book.close();
		} catch (FileNotFoundException e) {
			throw new AppException("");
		} catch (IOException e) {
			throw new AppException("");
		}

		StringBuffer sbSql = new StringBuffer();
		for (List<List<String>> sheet : listSheet) {
			String sql = TableBeanToSql(sheetValueToTableBean(sheet));
			sbSql.append(sql);
			sbSql.append("\r\n\r\n");
		}
		return sbSql.toString();
	}
}
